//	Copyright (c) 2003-2009 Olivier Giulieri - olivier@evolutility.org 

//	This file is part of Evolutility CRUD Framework.
//	Source link <http://www.evolutility.org/download/download.aspx>

//	Evolutility is free software: you can redistribute it and/or modify
//	it under the terms of the GNU General Public License as published by
//	the Free Software Foundation, either version 3 of the License, or
//	(at your option) any later version.

//	Evolutility is distributed in the hope that it will be useful,
//	but WITHOUT ANY WARRANTY; without even the implied warranty of
//	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//	GNU General Public License for more details.

//	You should have received a copy of the GNU General Public License
//	along with Evolutility. If not, see <http://www.gnu.org/licenses/>.


using System;
using System.Text; 
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Collections;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace Evolutility
{

	/* 
	This library is a dependency of : 
	 * Evolutility.DataServer 
	 * Evolutility.UIServer 
	 * Evolutility.Wizard 
	*/

    static class EvoDB
    {

        //### Constants ###################################################################################### 
        #region "Constants"

        //field types 
        internal const string t_text = "text";
        internal const string t_txtm = "textmultiline";
        internal const string t_bool = "boolean";
        internal const string t_dec = "decimal";
        internal const string t_int = "integer";
        internal const string t_date = "date";
        internal const string t_time = "time";
        internal const string t_datetime = "datetime";
        internal const string t_pix = "image";
        internal const string t_doc = "document";
        internal const string t_lov = "lov";
        internal const string t_formula = "formula";
        internal const string t_html = "html";
        internal const string t_email = "email";
        internal const string t_url = "url";

        //added by Sam, to support command
        internal const string t_command = "command";
        internal const string disable_command = "disabled=\"disabled\"";
        //--------------------------------

        internal const string p_itemid = "@itemid";
        internal const string p_userid = "@userid";

        internal const string SQL_WHERE = " WHERE ";
        internal const string SQL_EXEC = "EXEC ";
        internal const string SQL_NULL = "NULL";

        internal const string SQL_IDENTITY = " SELECT SCOPE_IDENTITY()"; // @@IDENTITY
        internal const string SQL_ID_INSERT = "SET IDENTITY_INSERT ";
        internal const string SQL_BEGIN_TRANS = "BEGIN TRANSACTION \r\n";
        internal const string SQL_COMMIT_TRANS = "\r\n COMMIT TRANSACTION \r\n";
        internal const string SQL_INCREMENT = "{0}=CASE WHEN({0} is null)THEN 1 ELSE {0}+1 END";  // will work for null values

        internal const string SQL_SELECT_LOV = "TOP {0} ID,rtrim({1}) AS value";
        internal const string SQL_SELECT_LOV_T = "TOP {0} t.ID,rtrim(t.{1}) AS value";

        internal const string SQLf_T01 = "T.{0}={1}";
        internal const string SQLf_T02 = "T.{0}='{1}'";
        internal const string SQLw_Sharing = "(T.Publish>0 OR T.{0}={1})";

        internal const string QueryError = "Cannot execute database query.";
        private const string SQL_and = " AND ";

        internal const string soEqual = "eq", soStartWith = "sw", soFinishWith = "fw", soContain = "ct", soBetween = "bt";
        internal const string soIsNull = "null", soIsNotNull = "nn";
        internal const string soGreaterThan = "gt", soSmallerThan = "st";

        #endregion

        //### Formatting & Escaping ###################################################################################### 
        #region "Formatting & Escaping"

        static internal string dbformat2(string myVal, string myType, string language)
        {
            //Used for Export 
            switch (myType)
            {
                case "System.String":
                    return string.Format("N'{0}'", SQLescape(myVal));
                case "System.Boolean":
                    if (myVal == "True")
                        return "1";
                    else
                        return EvoTC.StrVal(myVal);
                case "System.DateTime":
                    if (language == "FR")
                        return GetFrenchDate(myVal);
                    else
                    {
                        if (language == "DA")
                        {
                            if (EvoTC.isDate(myVal))
                                return string.Format("'{0}'", EvoTC.String2DateTime(myVal).ToString("G"));
                            else
                                return SQL_NULL;
                        }
                        else
                        {
                            if (EvoTC.isDate(myVal))
                                return string.Format("'{0}'", EvoTC.String2DateTime(myVal).ToString("yyyy-M-d hh:mm:ss tt"));
                            else
                                return SQL_NULL;
                        }
                    }
                default: //"System.Int32", "System.Byte", "System.Decimal" 
                    return EvoTC.StrVal(myVal);
            }
        }

        static internal string dbFormat(string fieldValue, string fieldType, int fieldMaxLength, string language)
        {
            switch (fieldType)
            {
                case t_text:
                case t_txtm:
                case t_pix:
                case t_doc:
                case t_email:
                case t_url:
                case t_html:
                    if (fieldMaxLength > 0 && fieldValue.Length > fieldMaxLength)
                        return string.Format("N'{0}'", fieldValue.Substring(0, fieldMaxLength).Replace("'", "''"));
                    else
                        return string.Format("N'{0}'", fieldValue.Replace("'", "''"));
                case t_lov:
                    if (string.IsNullOrEmpty(fieldValue))
                        return string.Empty;
                    else
                        return EvoTC.StrVal(fieldValue);
                case t_bool:
                case t_int:
                    if (!string.IsNullOrEmpty(fieldValue))
                    {
                        return EvoTC.StrVal(fieldValue);
                    }
                    else
                        return SQL_NULL;
                case t_dec:
                    string tDecStr;
                    if (EvoTC.isInteger(fieldValue))
                    {
                        //MUST NOT USE FORMATTED NUMBER IN EDIT GRID 
                        tDecStr = fieldValue.TrimStart();
                    }
                    else if (!string.IsNullOrEmpty(fieldValue))
                    {
                        tDecStr = EvoTC.String2Dec(fieldValue).ToString();
                    }
                    else
                        tDecStr = SQL_NULL;
                    if (language == "FR" || language == "DA")
                        tDecStr = tDecStr.Replace(",", ".");
                    return tDecStr;
                case t_date:
                case t_datetime:
                case t_time:
                    if (language == "FR")
                        return GetFrenchDate(fieldValue);
                    else if (language == "CHS")
                        return GetChineseDate(fieldValue, fieldType);
                    else
                    {
                        if ((language == "DA"))
                        {
                            if (EvoTC.isDate(fieldValue))
                            {
                                string df;
                                switch (fieldType)
                                {
                                    case t_date:
                                        df = "yyyy-M-d";
                                        break;
                                    case t_datetime:
                                        df = "yyyy-M-d HH:mm:ss";
                                        break;
                                    default:	//"time" 
                                        df = "HH:mm:ss";
                                        break;
                                }
                                return string.Format("'{0}'", EvoTC.String2DateTime(fieldValue).ToString(df));
                            }
                            else
                                return SQL_NULL;
                        }
                        else
                        {
                            if (EvoTC.isDate(fieldValue))
                            {
                                string df;
                                switch (fieldType)
                                {
                                    case t_date:
                                        df = "yyyy-M-d";
                                        break;
                                    case t_datetime:
                                        df = "yyyy-M-d hh:mm:ss tt";
                                        break;
                                    default:	//"time" 
                                        df = "hh:mm:ss tt";
                                        break;
                                }
                                return string.Format("'{0}'", EvoTC.String2DateTime(fieldValue).ToString(df));
                            }
                            else
                                return SQL_NULL;
                        }
                    }
                default:
                    return SQLescape(fieldValue);
            }
        }

        private static string GetChineseDate(string fieldValue, string fieldType)
        {

            if (String.IsNullOrEmpty(fieldValue))
                return SQL_NULL;

            if (EvoTC.isDate(fieldValue))
            {
                string df;
                switch (fieldType)
                {
                    case t_date:
                        df = "yyyy-M-d";
                        break;
                    case t_datetime:
                        df = "yyyy-M-d hh:mm:ss";
                        break;
                    default:	//"time" 
                        df = "hh:mm:ss";
                        break;
                }
                return string.Format("'{0}'", EvoTC.String2DateTime(fieldValue).ToString(df));
            }
            else
                return SQL_NULL;
        }

        static internal string SQLec(string SQLColumn, string FieldType, string FieldValue, string Operator)
        {
            return SQLec(SQLColumn, FieldType, FieldValue, "", Operator);
        }
        static internal string SQLec(string SQLColumn, string FieldType, string FieldValue, string FieldValue2, string Operator)
        {//returns a "condition" in SQL or plain English 
            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(SQLColumn))
                sb.Append(SQL_and);
            if (Operator == soIsNull)
                sb.AppendFormat("({0} IS NULL OR {0}='')", SQLColumn);
            else if (Operator == soIsNotNull)
                sb.AppendFormat("NOT({0} IS NULL OR {0}='')", SQLColumn);
            else
            {
                if (!string.IsNullOrEmpty(SQLColumn))
                    sb.Append(SQLColumn);
                //textmultiline is passed as text ! 
                if (FieldType == t_text)
                {
                    switch (Operator)
                    {
                        case soEqual:
                            sb.AppendFormat("=N'{0}'", FieldValue);
                            break;
                        case soStartWith:
                            sb.AppendFormat(" LIKE N'{0}%'", FieldValue);
                            break;
                        case soFinishWith:
                            sb.AppendFormat(" LIKE N'%{0}'", FieldValue);
                            break;
                        case soIsNull:
                            sb.AppendFormat(" LIKE N'%{0}'", FieldValue);
                            break;
                        case soIsNotNull:
                            sb.AppendFormat(" LIKE N'%{0}'", FieldValue);
                            break;
                        default:	// soContain
                            sb.AppendFormat(" LIKE N'%{0}%'", FieldValue);
                            break;
                    }
                }
                else if (((FieldType == t_date) || (FieldType == t_datetime) || (FieldType == t_time)) && (Operator == soBetween))
                {
                    sb.AppendFormat(" Between '{0}' and '{1}'", FieldValue, FieldValue2);
                }
                else
                    switch (Operator)
                    {
                        case soGreaterThan:
                            return ">";
                        case soSmallerThan:
                            return "<";
                        default:
                            return "=";
                    }
            }
            return sb.ToString();
        }

        static internal string SQLescape(string aString)
        {
            //simple SQL escaping to avoid SQL injection attack 
            if (string.IsNullOrEmpty(aString))
                return string.Empty;
            else
                return aString.Replace("'", "''");
        }

        static internal string SQLescape2(string aString)
        {
            //SQL escaping for WHERE clause w/ LIKE 
            if (string.IsNullOrEmpty(aString))
                return string.Empty;
            else
                return aString.Replace("[", "[[]").Replace("]", "[]]").Replace("%", "[%]").Replace("_", "[_]");
        }

        #endregion

        //### SP & dates ###################################################################################### 
        #region "SP & dates"

        static internal string SPcall_Paging(string SPname, string spSelect, string spFrom, string spWhere, string spOrderBy, string sqlPK, int spPageID, int spPageSize, int spUserID, string myDBtable)
        {
            string sql;
            sql = SPname.Replace("@SQLselect", string.Format("'{0}'", spSelect.Replace("'", "''")));
            sql = sql.Replace("@SQLtable", string.Format("'{0}'", myDBtable.Replace("'", "''")));
            sql = sql.Replace("@SQLfrom", string.Format("'{0}'", spFrom.Replace("'", "''")));
            sql = sql.Replace("@SQLwhere", string.Format("'{0}'", spWhere.Replace("'", "''")));
            sql = sql.Replace("@SQLorderby", string.Format("'{0}'", spOrderBy.Replace("'", "''")));
            sql = sql.Replace("@SQLpk", string.Format("'{0}'", sqlPK.Replace("'", "''")));
            sql = sql.Replace("@pageid", string.Format("'{0}'", spPageID));
            sql = sql.Replace("@pagesize", string.Format("'{0}'", spPageSize));
            return sql;
        }

        static internal string SPcall_Get(string SPname, int itemID, int userID)
        {
            //replace parameters by values in stored procedure SQL call 
            return SPname.Replace(p_itemid, itemID.ToString()).Replace(p_userid, userID.ToString());
        }
        static internal string SPcall_Get(string SPname, int itemID, int userID, int fieldID)
        {
            return SPcall_Get(SPname, itemID, userID).Replace("@fieldid", fieldID.ToString());
        }

        static internal string GetFrenchDate(string dateString)
        {
            bool ValidDate = false;
            if (string.IsNullOrEmpty(dateString))
                return SQL_NULL;
            char[] sepChars = { '/', '-' };
            string[] dateParts = dateString.Split(sepChars);
            if (dateParts.Length == 3)
            {
                if (EvoTC.String2Int(dateParts[2]) < 100)
                    dateParts[2] = (2000 + EvoTC.String2Int(dateParts[2])).ToString();
                if (ServerUsesFrenchDates())
                    ValidDate = EvoTC.isDate(dateString);
                else
                    ValidDate = EvoTC.isDate(string.Format("{1}/{0}/{2}", dateParts[0], dateParts[1], dateParts[2]));
                if (ValidDate)
                    return string.Format("convert(datetime,'{1}/{0}/{2}',101)", dateParts[0], dateParts[1], dateParts[2]);
                else
                    return SQL_NULL;
            }
            else
                return SQL_NULL;
        }

        static internal bool ServerUsesFrenchDates()
        {
            return EvoTC.isDate("16/1/2008");
        }

        static internal string wDateIsValue(string DBcolumn, string DBformatedDate, string DBoperator)
        {
            if (DBoperator == null)
                DBoperator = "=";
            return String.Format(" DATEDIFF(day,{0},{1}){2}0 ", DBformatedDate, DBcolumn, DBoperator);
        }

        static internal string wBoolIsFalse(string DBcolumn)
        {
            return String.Format("isnull({0},0)=0", DBcolumn);
        }

        #endregion

        //### SQL as usual ###################################################################################### 
        #region "SQL as usual"

        static internal string BuildSQL(string SQLselect, string SQLfrom, string SQLwhere, string SQLorderby)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT ").Append(SQLselect).Append(" FROM ").Append(SQLfrom);
            if (!string.IsNullOrEmpty(SQLwhere))
                sql.Append(SQL_WHERE).Append(SQLwhere);
            if (!string.IsNullOrEmpty(SQLorderby))
                sql.Append(" ORDER BY ").Append(SQLorderby);
            return sql.Append(";").ToString();
        }
        static internal string BuildSQL(string SQLselect, string SQLfrom)
        {
            return string.Format("SELECT {0} FROM {1};", SQLselect, SQLfrom);
        }

        static internal string sqlINSERT(string SQLTable, string SQLColumns, string SQLvalues)
        {
            return (new StringBuilder()).Append("INSERT INTO ").Append(SQLTable).Append("(").Append(SQLColumns).Append(") VALUES (").Append(SQLvalues).Append(");").ToString();
        }

        static internal string sqlUPDATE(string SQLTable, string SQLColumnsValuesTuples, string SQLWhere)
        {
            return (new StringBuilder()).Append("UPDATE ").Append(SQLTable).Append(" SET ").Append(SQLColumnsValuesTuples).Append(SQL_WHERE).Append(SQLWhere).ToString();
            // NO ";" 
        }

        static internal string sqlDELETE(string SQLTable, string SQLWhere)
        {
            return (new StringBuilder()).Append("DELETE FROM ").Append(SQLTable).Append(SQL_WHERE).Append(SQLWhere).ToString();
            // NO ";" 
        }

        static internal string sqlTRANSACTION(string mySQL)
        {
            return (new StringBuilder()).Append(SQL_BEGIN_TRANS).Append(mySQL).Append(SQL_COMMIT_TRANS).ToString();
        }

        #endregion

        //### DB Access ###################################################################################### 
        #region "DB Access"

        static internal DataSet GetData(string SQL, string mySqlConnection, ref string ErrorMsg)
        {
            //Run query and returns DataSet 
            SqlConnection myConnection = new SqlConnection(mySqlConnection);
            SqlDataAdapter myCommand = new SqlDataAdapter(SQL, myConnection);
            DataSet ds = new DataSet();

            try
            {
                myCommand.Fill(ds, SQL);
                return ds;
            }
            catch (Exception DBerror)
            {
                ErrorMsg += HTMLtextMore(QueryError, DBerror.Message);
                return null;
            }
            finally
            {
                myCommand.Dispose();
                myConnection.Close();
            }
        }

        static internal SqlDataReader GetDataReader(string SQL, string mySqlConnection)
        {
            SqlConnection myConnection = new SqlConnection(mySqlConnection);
            SqlCommand myCommand = new SqlCommand(SQL, myConnection);


            myConnection.Open();

            return myCommand.ExecuteReader();



        }

        static internal DataSet GetDataParameters(string SQL, string mySqlConnection, SqlParameter[] Params, ref string ErrorMsg)
        {
            SqlConnection myConnection = new SqlConnection(mySqlConnection);
            SqlDataAdapter myCommand = new SqlDataAdapter(SQL, myConnection);
            DataSet ds = new DataSet();

            try
            {
                SqlParameterCollection pc = myCommand.SelectCommand.Parameters;
                foreach (SqlParameter param in Params)
                {
                    pc.Add(param);
                }
                myCommand.Fill(ds, SQL);
                return ds;
            }
            catch (Exception DBerror)
            {
                ErrorMsg += HTMLtextMore(QueryError, DBerror.Message);
                return null;
            }
            finally
            {
                myCommand.Dispose();
                myConnection.Close();
            }
        }

        static internal string GetDataScalar(string SQL, string mySqlConnection, ref string ErrorMsg)
        {
            string ReturnValue = null;
            SqlConnection myConnection = new SqlConnection(mySqlConnection);
            SqlCommand myCommand = new SqlCommand(SQL, myConnection);

            try
            {
                myConnection.Open();
                ReturnValue = Convert.ToString(myCommand.ExecuteScalar());
            }
            catch (Exception DBerror)
            {
                ErrorMsg += HTMLtextMore(QueryError, DBerror.Message);
                ReturnValue = string.Empty;
            }
            finally
            {
                myCommand.Dispose();
                myConnection.Close();
            }
            return ReturnValue;
        }

        static internal string RunSQL(string SQL, string aSqlConnection, bool InTransaction)
        {
            SqlConnection myConnection = new SqlConnection(aSqlConnection);
            string ErrorMsg1 = string.Empty;

            if (!string.IsNullOrEmpty(SQL))
            {
                if (InTransaction)
                    SQL = sqlTRANSACTION(SQL);
                SqlCommand myCommand = new SqlCommand(SQL, myConnection);
                try
                {
                    myCommand.Connection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ErrorMsg1 = HTMLtextMore(QueryError, ex.ToString());
                }
                finally
                {
                    myCommand.Dispose();
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }
            return ErrorMsg1;
        }

        static internal bool ColumnExists(DataTable Table, string ColumnName)
        {
            bool isThere = false;
            foreach (DataColumn col in Table.Columns)
            {
                if (col.ColumnName == ColumnName)
                {
                    isThere = true;
                    break;
                }
            }
            return isThere;
        }

        #endregion

        //### Misc. ###################################################################################### 
        #region "Misc"

        // Having this function here saves the dependency on EvoLibUI which makes it easier to include.
        static private String HTMLtextMore(string myText, string myOptions)
        {
            StringBuilder zHTML = new StringBuilder();
            zHTML.Append(myText).Append("<div class=\"Foot\">").Append(myOptions).Append("</div>");
            return zHTML.ToString();
        }

        public static bool ExecuteSqlFile(string varFileName)
        {
            if (!File.Exists(varFileName))
            {
                return false;
            }

            StreamReader sr = File.OpenText(varFileName);

            ArrayList alSql = new ArrayList();

            string commandText = "";

            string varLine = "";

            while (sr.Peek() > -1)
            {
                varLine = sr.ReadLine();
                if (varLine == "")
                {
                    continue;
                }
                if (varLine != "GO")
                {
                    commandText += varLine;
                    commandText += "\r\n";
                }
                else
                {
                    alSql.Add(commandText);
                    commandText = "";
                }
            }

            sr.Close();

            try
            {
                ExecuteCommand(alSql);
            }
            catch(Exception ex)
            {
                throw ex;
            }

            return true;
        }


        private static void ExecuteCommand(ArrayList varSqlList)
        {
            Database db = DatabaseFactory.CreateDatabase("MRPSqlServer");
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {

                    foreach (string varcommandText in varSqlList)
                    {
                        db.ExecuteNonQuery(CommandType.Text, varcommandText);
                    }

                    // Commit the transaction.
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }

        }

        #endregion

    }
} 